/************************************************************************************/
/*  Program name CAPARIMP_MAPPING.sql                                               */
/*  Creater:  Francis Lee (PCCW - UniHub)                                           */
/*************   History   *************************                                */
/* 10 Feb, 2005   Francis Lee         First created                                 */
/************************************************************************************/

SET DEFINE OFF;
CREATE OR REPLACE PACKAGE CAPARIMP_MAPPING
IS
	FUNCTION GetTransactionTypeIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetTransactionSourceIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReceivableActivityIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReceiptSourceIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetMemoLineIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetSalesRepIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetDistributionSetIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZPartyIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZPartyNumberMapping(
		pOldID		VARCHAR2
	) RETURN VARCHAR2;
	
	FUNCTION GetHZPartySiteIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZPartySiteNumberMapping(
		pOldID		VARCHAR2
	) RETURN VARCHAR2;
	
	FUNCTION GetHZCustAccountIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZAccountNumberMapping(
		pOldID		VARCHAR2
	) RETURN VARCHAR2;
	
	FUNCTION GetHZCustAcctSiteIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZSiteUseIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZLocationIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZRelationshipIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetHZCustAccountRoleIDMapping(
	pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetInvoiceBatchIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetCustomerTrxIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetCustomerTrxLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetCustTrxLineSalesIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetCustTrxLineGLDistIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetPaymentScheduleIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReceiptBatchIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetCashReceiptIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetCashReceiptHistoryIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReceivableApplicationIDMap(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetAdjustmentIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetMiscCashDistributionIDMap(
		pOldID		NUMBER
	) RETURN NUMBER;
	
	FUNCTION GetReversalReceiptHistID(
		pOldID		NUMBER
	) RETURN NUMBER;
	
END CAPARIMP_MAPPING;
/
SET DEFINE OFF;

CREATE OR REPLACE PACKAGE BODY CAPARIMP_MAPPING
IS
/********************************************************/
	FUNCTION GetTransactionTypeIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			select cust_trx_type_id_n
			into   lv_NewID
			from   crst_ra_cust_trx_types_all
			where  org_id = pOrgID
			and    cust_trx_type_id = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetTransactionTypeIDMapping;
/********************************************************/
    FUNCTION GetTransactionSourceIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			select batch_source_id_n
			into   lv_NewID
			from   crst_ra_batch_sources_all
			where  org_id = pOrgID
			and    batch_source_id = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetTransactionSourceIDMapping;
/********************************************************/
    FUNCTION GetReceivableActivityIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			select receivables_trx_id_n
			into   lv_NewID
			from   crst_ar_receivables_trx_all
			where  org_id = pOrgID
			and    receivables_trx_id = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReceivableActivityIDMapping;
/********************************************************/
    FUNCTION GetReceiptSourceIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			select batch_source_id_n
			into   lv_NewID
			from   crst_ar_batch_sources_all
			where  org_id = pOrgID
			and    batch_source_id = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReceiptSourceIDMapping;		
/********************************************************/
    FUNCTION GetMemoLineIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			select memo_line_id_n
			into   lv_NewID
			from   crst_ar_memo_lines_all_b
			where  org_id = pOrgID
			and    memo_line_id = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetMemoLineIDMapping;		
/********************************************************/
    FUNCTION GetSalesRepIDMapping(
		pOrgID		NUMBER,
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			select salesrep_id_n
			into   lv_NewID
			from   crst_jtf_rs_salesreps
			where  org_id = pOrgID
			and    salesrep_id = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetSalesRepIDMapping;						
/********************************************************/
	FUNCTION GetDistributionSetIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	DISTRIBUTION_SET_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_DISTRIBUTION_SETS_ALL
			WHERE	DISTRIBUTION_SET_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetDistributionSetIDMapping;	
/********************************************************/
    FUNCTION GetHZPartyIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	PARTY_ID_N
			INTO	lv_NewID
			FROM	CRST_HZ_PARTIES
			WHERE	PARTY_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZPartyIDMapping;			
/********************************************************/
    FUNCTION GetHZPartyNumberMapping(
		pOldID		VARCHAR2
	) RETURN VARCHAR2 IS
		lv_NewID		VARCHAR2(30);
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	PARTY_NUMBER_N
			INTO	lv_NewID
			FROM	CRST_HZ_PARTIES
			WHERE	PARTY_NUMBER = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZPartyNumberMapping;			
/********************************************************/
    FUNCTION GetHZPartySiteIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	PARTY_SITE_ID_N
			INTO	lv_NewID
			FROM	CRST_HZ_PARTY_SITES
			WHERE	PARTY_SITE_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZPartySiteIDMapping;			
/********************************************************/
    FUNCTION GetHZPartySiteNumberMapping(
		pOldID		VARCHAR2
	) RETURN VARCHAR2 IS
		lv_NewID		VARCHAR2(30);
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	PARTY_SITE_NUMBER_N
			INTO	lv_NewID
			FROM	CRST_HZ_PARTY_SITES
			WHERE	PARTY_SITE_NUMBER = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZPartySiteNumberMapping;			
/********************************************************/
	FUNCTION GetHZCustAccountIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	CUST_ACCOUNT_ID_N
			INTO	lv_NewID
			FROM	CRST_HZ_CUST_ACCOUNTS
			WHERE	CUST_ACCOUNT_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZCustAccountIDMapping;			
/********************************************************/
    FUNCTION GetHZAccountNumberMapping(
		pOldID		VARCHAR2
	) RETURN VARCHAR2 IS
		lv_NewID		VARCHAR2(30);
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	ACCOUNT_NUMBER_N
			INTO	lv_NewID
			FROM	CRST_HZ_CUST_ACCOUNTS
			WHERE	ACCOUNT_NUMBER = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZAccountNumberMapping;			
/********************************************************/
    FUNCTION GetHZCustAcctSiteIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	CUST_ACCT_SITE_ID_N
			INTO	lv_NewID
			FROM	CRST_HZ_CUST_ACCT_SITES_ALL
			WHERE	CUST_ACCT_SITE_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZCustAcctSiteIDMapping;			
/********************************************************/
    FUNCTION GetHZSiteUseIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	SITE_USE_ID_N
			INTO	lv_NewID
			FROM	CRST_HZ_CUST_SITE_USES_ALL
			WHERE	SITE_USE_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZSiteUseIDMapping;			
/********************************************************/
    FUNCTION GetHZLocationIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	LOCATION_ID_N
			INTO	lv_NewID
			FROM	CRST_HZ_LOCATIONS
			WHERE	LOCATION_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZLocationIDMapping;			
/********************************************************/
    FUNCTION GetHZRelationshipIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	DISTINCT(RELATIONSHIP_ID_N)
			INTO	lv_NewID
			FROM	CRST_HZ_RELATIONSHIPS
			WHERE	RELATIONSHIP_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZRelationshipIDMapping;			
/********************************************************/
    FUNCTION GetHZCustAccountRoleIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	CUST_ACCOUNT_ROLE_ID_N
			INTO	lv_NewID
			FROM	CRST_HZ_CUST_ACCOUNT_ROLES
			WHERE	CUST_ACCOUNT_ROLE_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetHZCustAccountRoleIDMapping;			
/********************************************************/
    FUNCTION GetInvoiceBatchIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	BATCH_ID_N
			INTO	lv_NewID
			FROM	CRST_RA_BATCHES_ALL
			WHERE	BATCH_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetInvoiceBatchIDMapping;			
/********************************************************/
    FUNCTION GetCustomerTrxIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	CUSTOMER_TRX_ID_N
			INTO	lv_NewID
			FROM	CRST_RA_CUSTOMER_TRX_ALL
			WHERE	CUSTOMER_TRX_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetCustomerTrxIDMapping;			
/********************************************************/
    FUNCTION GetCustomerTrxLineIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	CUSTOMER_TRX_LINE_ID_N
			INTO	lv_NewID
			FROM	CRST_RA_CUSTOMER_TRX_LINES_ALL
			WHERE	CUSTOMER_TRX_LINE_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetCustomerTrxLineIDMapping;			
/********************************************************/
    FUNCTION GetCustTrxLineSalesIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	CUST_TRX_LINE_SALESREP_ID_N
			INTO	lv_NewID
			FROM	CRST_RA_CUST_TRX_LINE_SALESREP
			WHERE	CUST_TRX_LINE_SALESREP_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetCustTrxLineSalesIDMapping;			
/********************************************************/
    FUNCTION GetCustTrxLineGLDistIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	CUST_TRX_LINE_GL_DIST_ID_N
			INTO	lv_NewID
			FROM	CRST_RA_CUST_TRX_LINE_GL_DIST
			WHERE	CUST_TRX_LINE_GL_DIST_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetCustTrxLineGLDistIDMapping;			
/********************************************************/
    FUNCTION GetPaymentScheduleIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	PAYMENT_SCHEDULE_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_PAYMENT_SCHEDULES_ALL
			WHERE	PAYMENT_SCHEDULE_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetPaymentScheduleIDMapping;			
/********************************************************/
    FUNCTION GetReceiptBatchIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	BATCH_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_BATCHES_ALL
			WHERE	BATCH_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReceiptBatchIDMapping;			
/********************************************************/
    FUNCTION GetCashReceiptIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	CASH_RECEIPT_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_CASH_RECEIPTS_ALL
			WHERE	CASH_RECEIPT_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetCashReceiptIDMapping;			
/********************************************************/
    FUNCTION GetCashReceiptHistoryIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	CASH_RECEIPT_HISTORY_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_CASH_RECEIPT_HISTORY
			WHERE	CASH_RECEIPT_HISTORY_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetCashReceiptHistoryIDMapping;			
/********************************************************/
    FUNCTION GetReceivableApplicationIDMap(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	RECEIVABLE_APPLICATION_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_RECEIVABLE_APPLICATION
			WHERE	RECEIVABLE_APPLICATION_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReceivableApplicationIDMap;			
/********************************************************/
    FUNCTION GetAdjustmentIDMapping(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			BEGIN
			SELECT	ADJUSTMENT_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_ADJUSTMENTS_ALL
			WHERE	ADJUSTMENT_ID = pOldID;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					lv_NewID := pOldID;
			END;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetAdjustmentIDMapping;			
/********************************************************/
    FUNCTION GetMiscCashDistributionIDMap(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_NewID		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	MISC_CASH_DISTRIBUTION_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_MISC_CASH_DISTRIBUTION
			WHERE	MISC_CASH_DISTRIBUTION_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetMiscCashDistributionIDMap;			
/********************************************************/
	FUNCTION GetReversalReceiptHistID(
		pOldID		NUMBER
	) RETURN NUMBER IS
		lv_Newid		NUMBER;
	BEGIN
		IF	pOldID	IS	NOT NULL	THEN
			SELECT	CASH_RECEIPT_HISTORY_ID_N
			INTO	lv_NewID
			FROM	CRST_AR_CASH_RECEIPT_HISTORY
			WHERE	CASH_RECEIPT_HISTORY_ID = pOldID;
		ELSE
			lv_NewID	:=	NULL;
		END IF;
		RETURN(lv_NewID);
	END GetReversalReceiptHistID;
/********************************************************/
END CAPARIMP_MAPPING;
/
